import pyodbc
from decimal import Decimal
import mysql.connector

class SqlServerConn():
    def __init__(self,type,server,port,username,password,database='master'):
        if type=='sqlserver':
            self.con = pyodbc.connect(f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server},{port};DATABASE={database};UID={username};PWD={password}')
        else:
            self.con= mysql.connector.connect(
                host=server,
                port=port,
                user=username,
                password=password,
                database=database)

    def close_conn(self):
        self.con.close()

    def get_num(self, originalSql):
        cursor = self.con.cursor()
        cursor.execute(originalSql)
        result = cursor.fetchone()
        if result is None or result[0] is None:
            return Decimal('0.00')
        else:
            cursor.fetchall()
            cursor.close()
            return result[0]

    def trans_dict(self,cursor):
        data=[]
        columns = [column[0] for column in cursor.description]
        for row in cursor.fetchall():
            result = dict(zip(columns, row))
            data.append(result)
        return data

    #将查询结果以列表字典返回
    def get_dict(self,originalSql):
        data=[]
        cursor = self.con.cursor()
        cursor.execute(originalSql)
        if cursor.description:
            data=self.trans_dict(cursor)
        else:
            while True:
                # 移动到下一个结果集
                has_next_result_set = cursor.nextset()
                # 判断结果集是否有数据
                if cursor.description:
                    data = data+self.trans_dict(cursor)
                if not has_next_result_set:
                    break
        return data


    # def exec_query_sql_one(self,originalSql, conn, str):
    #     cursor = conn.cursor()
    #     if str is not None:
    #         for key, value in str.items():
    #             originalSql = originalSql.replace('${' + key + '}', value)
    #     # print(originalSql)
    #     cursor.execute(originalSql)
    #     result = cursor.fetchone()
    #     if result is None or result[0] is None:
    #         return Decimal('0.00')
    #     else:
    #         cursor.fetchall()  # 读取完整的结果集
    #         cursor.close()  # 关闭游标对象
    #         return result[0]


    #将查询结果以列表字典返回
    # def exec_query_sql_more(self,originalSql,conn,str=None):
    #     data=[]
    #     cursor = conn.cursor()
    #     if str is not None:
    #         for key,value in str.items():
    #             originalSql= originalSql.replace('${'+key+'}',value)
    #     # print(originalSql)
    #     cursor.execute(originalSql)
    #     if  cursor.description:
    #         columns = [column[0] for column in cursor.description]
    #         for row in cursor.fetchall():
    #             result = dict(zip(columns, row))
    #             data.append(result)
    #     else:
    #         while True:
    #             # 移动到下一个结果集
    #             has_next_result_set = cursor.nextset()
    #             # 判断结果集是否有数据
    #             if cursor.description:
    #                 #打印成字典类型
    #                 columns = [column[0] for column in cursor.description]
    #                 for row in cursor.fetchall():
    #                     result = dict(zip(columns, row))
    #                     data.append(result)
    #             if not has_next_result_set:
    #                 break
    #     return data